﻿using System;
using System.Text;
using System.Data;
using CRMFramework;

namespace CRMBiz
{
    public static class DuplicateContactHandler
    {
        public static DataSet SelectDuplicateContact(int pageIndex, int pageSize, string searchfield)
        {
            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("Select table1.* from (Select ");
                string[] columns = { };
                string strquery = string.Empty;
                String strWhr = " Where ISNULL(DupwithContact) AND  deletedFlag=0 ";
                String strName = " '<span style=\"color:green\">',trim(CONCAT(UCASE(SUBSTRING(FirstName, 1, 1)),LOWER(SUBSTRING(FirstName, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(MiddleName, 1, 1)),LOWER(SUBSTRING(MiddleName, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(SurName, 1, 1)),LOWER(SUBSTRING(SurName, 2)))) ,'</span>' ";
                String strLeftJoin = " LEFT JOIN  (Select DISTINCT DupwithContact  as tempid from crm_contact where DupwithContact is not null  AND  DupwithContact<>0 AND  DupwithContact <> '') tblDuplicat1 " +
                                       " ON  table1.ContactID=tblDuplicat1.tempid where ISNULL(tblDuplicat1.tempid) ";

                if (!string.IsNullOrEmpty(searchfield))
                {
                    if (searchfield.Contains("/"))  // Multiple
                    {
                        columns = searchfield.Split('/');
                        if (columns[0] == "FullName")
                            if (columns[1] != "app_ipaddress")
                            {

                                strquery = " INNER JOIN (Select FirstName as fname, MiddleName as mname,SurName as sname ," + columns[1] + " as dob ,count(*) as Total FROM crm_contact " +
                                         "  AND  FirstName IS NOT Null AND trim(FirstName) <> '' " +
                                        "  AND  MiddleName IS NOT Null AND trim(MiddleName) <> '' " +
                                           "  AND  SurName IS NOT Null AND trim(SurName) <> '' " +
                                              "  AND  " + columns[1] + " IS NOT Null AND trim(" + columns[1] + ") <> '' " +
                                    " GROUP BY FirstName,MiddleName,SurName," + columns[1] + " having count(*) > 1 ) tblDup " +
                                    " Where trim(tblDup.fname)=trim(FirstName) and trim(tblDup.mname)=trim(MiddleName) and trim(tblDup.sname)=trim(SurName)  and trim(tblDup.dob)=trim(" + columns[1] + ")";

                                sbSql.Append(" CAST(CONCAT(" + strName + ",'   ','<span style=\"color:orange;padding-left:10px\">', trim(" + columns[1] + "),'</span>') AS Char) as FullName,")
                                     .Append(" CAST(CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName) ,' ', trim(" + columns[1] + ")) AS Char) as DetectiveBy,")
                                     .Append(" ContactID, CreateDate,app_id,tblDup.total as DuplicateCount, ")
                                     .Append(" (Select count(*)  from (Select ContactID FROM crm_contact " + strquery + " )table1 " + strLeftJoin)
                                     .Append(" ) AS Total FROM crm_contact ")
                                     .Append(strquery);
                            }
                            else
                            {
                                strquery = " INNER join crm_loaninfo on crm_contact.app_id=crm_loaninfo.app_id " +
                                            " INNER JOIN (Select  FirstName as fname, MiddleName as mname,SurName as sname ,crm_loaninfo." + columns[1] + " as dob ,count(*) as Total FROM crm_contact " +
                                            " INNER join crm_loaninfo on crm_contact.app_id=crm_loaninfo.app_id " + strWhr +
                                             "  AND  FirstName IS NOT Null AND trim(FirstName) <> '' " +
                                             "  AND  MiddleName IS NOT Null AND trim(MiddleName) <> '' " +
                                             "  AND  SurName IS NOT Null AND trim(SurName) <> '' " +
                                              "  AND  " + columns[1] + " IS NOT Null AND trim(" + columns[1] + ") <> '' " +
                                            " GROUP BY FirstName,MiddleName,SurName,crm_loaninfo." + columns[1] + " having count(*) > 1 ) tblDup " +
                                            " Where trim(tblDup.fname)=trim(FirstName) and trim(tblDup.mname)=trim(MiddleName) and trim(tblDup.sname)=trim(SurName)  and trim(tblDup.dob)=trim(crm_loaninfo." + columns[1] + ")";

                                sbSql.Append("CAST(CONCAT(" + strName + " ,' ','<span style=\"color:orange;padding-left:10px\">', trim(crm_loaninfo." + columns[1] + "),'</span>') AS Char) as FullName,")
                                .Append(" CAST(CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName) ,'    ', trim(crm_loaninfo." + columns[1] + ")) AS Char) as DetectiveBy,")
                                .Append(" ContactID, CreateDate,crm_contact.app_id,tblDup.total as DuplicateCount, ")
                                .Append(" (Select count(*)  from (Select ContactID FROM crm_contact " + strquery + " )table1 " + strLeftJoin)
                                .Append(" ) AS Total FROM crm_contact ")
                                .Append(strquery);
                            }
                    }
                    else  //Single
                    {
                        if (searchfield == "FullName")
                        {
                            strquery = " INNER JOIN (Select FirstName as fname, MiddleName as mname,SurName as sname ,count(*) as Total FROM crm_contact " +
                                 " AND FirstName IS NOT Null AND trim(FirstName) <> '' " +
                                    " AND MiddleName IS NOT Null AND trim(MiddleName) <> '' " +
                                       " AND SurName IS NOT Null AND trim(SurName) <> '' " +
                                        " GROUP BY FirstName,MiddleName,SurName having count(*) > 1 ) tblDup " +
                                        " Where trim(tblDup.fname)=trim(FirstName) and trim(tblDup.mname)=trim(MiddleName) and trim(tblDup.sname)=trim(SurName) ";

                            sbSql.Append(" CAST(CONCAT(" + strName + ") as Char) as FullName,")
                                 .Append(" CAST(CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName)) AS CHAR) as DetectiveBy,")
                                 .Append(" ContactID, CreateDate,app_id,tblDup.total as DuplicateCount, ")
                                 .Append(" (Select count(*)  from (Select ContactID FROM crm_contact " + strquery + " )table1 " + strLeftJoin)
                                 .Append(" ) AS Total FROM crm_contact ")
                                 .Append(strquery);
                        }
                        else if (searchfield == "Address")
                        {
                            strquery = " INNER JOIN (Select Street as fname, Suburb as mname,State as sname , PostCode as pin,count(*) as Total FROM crm_contact " + strWhr +
                                        " AND Street IS NOT Null AND trim(Street) <> '' " +
                                        " AND Suburb IS NOT Null AND trim(Suburb) <> '' " +
                                       " AND State IS NOT Null AND trim(State) <> '' " +
                                       " AND PostCode IS NOT Null AND trim(PostCode) <> '' " +
                                        " GROUP BY Street,Suburb,State,PostCode having count(*) > 1 ) tblDup " +
                                        " Where trim(tblDup.fname)=trim(Street) and trim(tblDup.mname)=trim(Suburb) and trim(tblDup.sname)=trim(State) and trim(tblDup.pin)=trim(PostCode) ";

                            sbSql.Append("CAST(CONCAT('<span style=\"color:green\">',trim(CONCAT(UCASE(SUBSTRING(Street, 1, 1)),LOWER(SUBSTRING(Street, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(Suburb, 1, 1)),LOWER(SUBSTRING(Suburb, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(State, 1, 1)),LOWER(SUBSTRING(State, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(PostCode, 1, 1)),LOWER(SUBSTRING(PostCode, 2)))),'</span>') AS Char) as FullName,")
                                .Append(" CAST(CONCAT(trim(Street),' ',trim(Suburb),' ',trim(State),' ',trim(PostCode)) AS CHAR) as DetectiveBy,")
                                .Append(" ContactID, CreateDate,app_id,tblDup.total as DuplicateCount, ")
                                .Append(" (Select count(*) from ( select ContactID from crm_contact " + strquery + " )table1 " + strLeftJoin)
                                .Append(" ) as Total FROM crm_contact ")
                                .Append(strquery);
                        }
                        else
                        {
                            strquery = " INNER JOIN (select  " + searchfield + " as DetectiveBy ,count(*) as Total FROM crm_contact " + strWhr +
                                         "  AND  " + searchfield + " IS NOT Null AND trim(" + searchfield + ") <> '' " +
                                         " GROUP BY " + searchfield + "  having total > 1 ) temp ON trim( " + searchfield + ")=trim(temp.DetectiveBy) ";

                            sbSql.Append(" CAST(CONCAT('<span style=\"color:green\">',trim(CONCAT(LOWER(SUBSTRING(" + searchfield + ", 1, 1)),LOWER(SUBSTRING(" + searchfield + ", 2)))),'</span>') AS CHAR) as FullName, trim(" + searchfield + ") as DetectiveBy, ContactID, CreateDate,app_id,temp.total as DuplicateCount, ")
                                .Append(" (Select count(*) from ( select ContactID from crm_contact " + strquery + " )table1 " + strLeftJoin)
                                .Append(") as Total FROM crm_contact ")
                                .Append(strquery);
                        }
                    }
                }
                else
                {
                    // Defalut Search
                    strquery = " INNER JOIN (Select FirstName as fname, MiddleName as mname,SurName as sname ,DateBirth as dob ,count(*) as Total FROM crm_contact " + strWhr +
                                 "  AND  FirstName IS NOT Null AND trim(FirstName) <> '' " +
                                 "  AND  MiddleName IS NOT Null AND trim(MiddleName) <> '' " +
                                 "  AND  SurName IS NOT Null AND trim(SurName) <> '' " +
                                 "  AND  DateBirth IS NOT Null AND trim(DateBirth) <> '' " +
                               " GROUP BY FirstName,MiddleName,SurName,DateBirth having count(*) > 1 ) tblDup " +
                               " Where trim(tblDup.fname)=trim(FirstName) and trim(tblDup.mname)=trim(MiddleName) and trim(tblDup.sname)=trim(SurName)  and trim(tblDup.dob)=trim(DateBirth)";

                    sbSql.Append(" CAST(CONCAT(" + strName + ",' ','<span style=\"color:orange;padding-left:10px\">', trim(DateBirth),'</span>') as Char) as FullName, ")
                        .Append(" CAST(CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName) ,' ', trim(DateBirth)) AS Char) as DetectiveBy,")
                        .Append(" ContactID, CreateDate,app_id,tblDup.total as DuplicateCount ,")
                        .Append(" (Select count(*) from ( select ContactID from crm_contact " + strquery + " )table1 " + strLeftJoin)
                        .Append(" ) as Total FROM crm_contact ")
                        .Append(strquery);
                }

                sbSql.Append(" )table1 " + strLeftJoin)
                    .Append(" ORDER BY  DuplicateCount Desc,DetectiveBy ");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetDuplicatClients(string field, string value)
        {
            DataSet ds;
            try
            {
                string strQuery = "Select ContactID from crm_contact where ";
                string[] columns = { };
                if (field.Contains("/"))  // Multiple
                {
                    columns = field.Split('/');
                    if (columns[1] != "app_ipaddress")
                        strQuery += " CAST(Concat(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName),' ' ,trim(" + columns[1] + ")) as Char)='" + value + "'";
                    else
                        strQuery = "select ContactID from crm_contact inner join crm_loaninfo on crm_loaninfo.app_id=crm_contact.app_id " +
                            " where  CAST(Concat(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName),' ',trim(crm_loaninfo.app_ipaddress)) as Char)='" + value + "'";
                }
                else  //Single
                {
                    if (field == "FullName")
                        strQuery += " CAST(Concat(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName)) as Char)='" + value + "'";
                    else if (field == "Address")
                        strQuery += " CAST(CONCAT(trim(Street),' ',trim(Suburb),' ',trim(State),' ',trim(PostCode)) AS CHAR)='" + value + "'";
                    else
                        strQuery += field + "= '" + value + "'";
                }
                strQuery += " AND deletedFlag=0 AND ISNULL(DupwithContact)";
                ds = DBhelper.ExecuteDataset(strQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static bool DeleteMultipleApplication(string applications)
        {
            bool flag = false;
            try
            {
                string query = "  Update crm_application SET deletedFlag=1 where app_id IN (" + applications + ");" +
                        "  Update crm_applicationcontact SET deletedFlag=1 where app_id IN (" + applications + ");";
                DBhelper.ExecuteNonQuery(query);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }
        public static bool DeleteDuplicateContact(string Contacts, int DupWithContact)
        {
            bool flag = false;
            try
            {
                string query = "Update crm_contact SET DupwithContact=" + DupWithContact + " WHERE ContactID IN (" + Contacts + ") ";
                DBhelper.ExecuteNonQuery(query);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        // This method is called from CompareDuplicate Page  

        public static bool duplicateMultipleClient(string allClient, int duplicateWith)
        {
            bool flag = false;
            try
            {
                string query = " Update crm_contact SET DupwithContact=" + duplicateWith + " WHERE ContactID IN (" + allClient + ");Update crm_applicationcontact SET ContactID=" + duplicateWith + "  where ContactID IN(" + allClient + ");" +
                               " Update crm_vedacreditcheckhistory SET CurrentContactID=" + duplicateWith + " where CurrentContactID IN (" + allClient + ");";

                DBhelper.ExecuteNonQuery(query);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        private static DataTable GenerateTransposedTable(DataTable inputTable)
        {
            DataTable outputTable = new DataTable();

            // Add columns by looping rows

            // Header row's first column is same as in inputTable
            outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

            // Header row's second column onwards, 'inputTable's first column taken
            foreach (DataRow inRow in inputTable.Rows)
            {
                string newColName = inRow[0].ToString();
                outputTable.Columns.Add(newColName);
            }

            // Add rows by looping columns        
            for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
            {
                DataRow newRow = outputTable.NewRow();

                // First column is inputTable's Header row's second column
                newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
                for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
                {
                    string colValue = inputTable.Rows[cCount][rCount].ToString();
                    newRow[cCount + 1] = colValue;
                }
                outputTable.Rows.Add(newRow);
            }

            return outputTable;
        }
        public static DataTable CompareDuplicateContact(int ContactID, string FirstName, string SurName, string DateBirth, string EMail,string secondEmailAddress, string Mobile, string Street, string State, string Suburb)
        {
            DataRow dr;
            DataTable dt = null;
            try
            {
                dt = new DataTable();
                string[] columns = { };
                string strWhere = " OR ( Email='" + EMail + "' AND Email IS NOT NULL AND Email <>'' ) OR ( secondEmailAddress='" + secondEmailAddress + "' AND secondEmailAddress IS NOT NULL AND secondEmailAddress <>'' )  OR ( Mobile='" + Mobile + "' AND Mobile IS NOT NULL AND Mobile <>'' ) OR (Street='" + Street + "' AND State='" + State + "' AND Suburb='" + Suburb + "'))";

                string sQuery = "Select ContactID,CONCAT(FirstName, ' ',MiddleName,' ',SurName) as FullName,CAST(Date_Format(crm_contact.CreateDate,'%d-%b-%Y %T') AS Char) as CreateDate, IFNULL(CAST(DATE_FORMAT(DateBirth,'%d-%b-%Y') as Char),'') as DateBirth," +
                                " Mobile,HomePhone,Email,DriverLicense,Citizenship,Street,Suburb,State,PostCode ,crm_contact.app_id as CreateByAppForm , CAST(Date_Format(app_info.app_Date,'%d-%b-%Y ') AS Char) as FormDate FROM crm_contact " +
                                " Left Join app_info on app_info.app_id=crm_contact.app_id " +
                                " where crm_contact.deletedFlag=0 and DupwithContact IS NULL AND ((FirstName='" + FirstName + "' AND SurName='" + SurName + "' AND  DateBirth='" + DateBirth + "')" + strWhere;
                sQuery += " ORDER By ContactID Desc ";

                DataSet ds = DBhelper.ExecuteDataset(sQuery);

                dt = GenerateTransposedTable(ds.Tables[0]);

                dr = dt.NewRow();
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Application Profile";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Loan Amount";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Submit Date";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Security";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Give each person No";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Client-Profile Action";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Application Profile (Delete)";
                dt.Rows.Add(dr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}